# test01_mysql.py
# 练习 ：员工表 (EMPLOYEE) 相关操作
# 员工表有 FIRST_NAME VARCHAR(20)，LAST_NAME VARCHAR(20)，
#         AGE INT，SEX VARCHAR(1)，INCOME FLOAT 字段
import pymysql
# 1. 建立连接
conn = pymysql.connect(
    host="127.0.0.1", port=3306, user="root",
    db="tedu1", charset="utf8")
# 2. 创建游标
cur = conn.cursor()
# ====================3.1 创建 EMPLOYEE 表=====================
# create_emp = "CREATE TABLE EMPLOYEE(FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), AGE INT, SEX VARCHAR(1),INCOME FLOAT);"
# cur.execute(create_emp)
# ====================3.2 添加数据=====================
# insert_emp = "INSERT INTO EMPLOYEE VALUES(%s, %s, %s, %s, %s);"
# cur.executemany(insert_emp, [
#     ('Mac', 'A', 20, 'M', 20000),
#     ('Tom', 'B', 20, 'F', 30000),
#     ('Bob', 'C', 20, 'M', 40000)
# ])
# ====================3.3 查询所有用户信息=====================
# select_emp = "SELECT * FROM EMPLOYEE;"
# cur.execute(select_emp)
# print(cur.fetchall())
# ====================3.4 更新数据============================
# update_emp="UPDATE EMPLOYEE SET INCOME = %s WHERE FIRST_NAME = %s;"
# cur.execute(update_emp, (10000, "Mac"))
# ====================3.5 删除数据============================
delete_emp = "DELETE FROM EMPLOYEE WHERE FIRST_NAME = %s;"
cur.execute(delete_emp, ("Tom", ))
# 4. 提交写请求和关闭资源
conn.commit()
cur.close()
conn.close()

# 1. 使用 PyMySQL 添加以下三条数据
#   'Mac', 'A', 20, 'M', 20000
#   'Tom', 'B', 20, 'F', 30000
#   'Bob', 'C', 20, 'M', 40000
# 2. 使用 PyMySQL 查询所有用户信息，并打印结果
# 3. 将 FIRST_NAME 为 Mac 的用户工资(INCOME字段)改成 10000
# 4. 删除 FIRST_NAME 为 Tom 的用户信息
